ASCII File Import

The ASCII File Import - this option is very useful and very powerful. It can eliminate much data entry if done properly! But there are many required formats and parameters that must be followed. Be sure to make the proper choices. An incorrect choice could inadvertently remove debts or duplicate existing debts. Someone with an understanding of ASCII data and layout needs to be involved until the process becomes very familiar.

The Clearinghouse strongly suggests you submit the ASCII file to us (through our secure website, NOT via email) so we can view it and ensure it meets all the proper requirements. We would also recommend you allow us to connect to your workstation and assist with the import. We do this for some local governments as often as every two weeks.

- this menu requires access rights assigned in the User Setup.

- it is recommended each time before an ASCII  Import to make a backup of your data PRIOR to the Excel import. In addition you should run a Summary Report so you can review the totals before and after to be sure the import was successful. 

Requirements:

• Click here for the required ASCII Layout

• Layout must match exactly
• You can leave optional areas blank
• Do No go beyond position 165

• No restriction on the ASCII file name
• Can only have one Account Code/Department for each ASCII file
• If possible, try to determine the total of the Debt Amount (positions 79-85) to verify once the import is completed
• Exit the software after each import ASCII, if doing multiple imports

 

•  SSN:
  • Must have leading zeroes with NO hyphens
  • Cannot be a Federal (FEIN) or State Identification Number
  • Can be an Individual Tax identification Number (ITIN)
• Name:
  • First Name cannot be blank/empty
  • Last Name cannot be blank/empty
  • Cannot be a Business Name
• Debt Amount:
  • Must have leading zeroes and no decimal point
•  Agency Code:
  • Must match the Assigned Code, to verify check the Tools-Account Information Entity Code
•  Expiration Date:
  • Must be the format of Date YYYYMM
• Compliance Date:
  • Must be the format MMDDYY
•  Unique Key:
  • Cannot be blank/empty
  • Can be up to 20 characters (letters, numbers and/or special characters), with 1st eight in pos. 129-136 and remainder in pos. 154-165
  • Will be saved and must be used as Unique Key when doing the Import ASCII-Adjustment option to modify the debt amounts
  • Stored and appears as "Import Key" if scroll to the right on the Debtor screens
  • Appears as "Import Key" in the Export Excel File
  • A separate internal Unique Key is also created and passed to the Clearinghouse that cannot be controlled or modified by the local governments
•  Account Code:
  • Cannot be blank/empty
  • Must be three characters
  • Must already be defined in the Tools-Administrator-Account Code
  • The User Name that was signed on must have the "ACCESS" setting for the account code

1. From the Main Menu click Imports:

2. Move the mouse over ASCII File Import and click this option:

 3. The following appears:

4. Click the down arrow for the following options:

• New Adds - will add each debt in the ASCII file as a new debt in the software. If the SSN and Unique Key are already in the software the debt will be rejected
• Only Adjustments - will use the Unique Key in ASCII file to find existing debt in software and update debt information. If the SSN and Unique Key are not already in the software the debt will be rejected
• New Adds and Adjustments - will add debts not found by SSN and Unique Key and those that are will be updated/adjusted to the debt amount in the import file

5. Click the down arrow for the following options:

- only the account codes you have access to will appear, select one account code or ALL. The ASCII import file must have the three character code in the Account Code/Department , positions 137-139

6. Click the desired Account Code

7. Click the Update Option:

• Retain Current Debts and add the ASCII file - keeps all debts and just adds the debts in the import Excel file
• Remove All Current debts and replace with the ASCII file - removes all debts with the Account Code selected and imports the debts in the import file.
  • when doing this option you will lose some reporting and historical information on the debts that were removed, even if they are in the ASCII file to be imported with the same Unique key
  • The Setoff report is one that cannot be regenerated
  • this option should be for a major correction of an entire account code or when a vendor/third-party administrator generates an updated file which has balances that were adjusted from previous setoffs

8. Click and find the file in the folder on the workstation or network, such as:

9. Click for the following:

10. Select one of the two options:

• Click to abort and return back to the Main Menu
• Click to initiate to process and continue

 

11. If the ASCII import layout meets the EXACT layout required, including column headings, then the following appears:

• the file was accepted and processed

 

• if the layout did not match and the following message appears:
• 

• Click for the following:

 

• The entire file was NOT imported
• Make the corrections to meet the required layout and retry
• Some of the possible errors:
  • SSN is not nine digits
  • First Name is empty
  • Last Name is empty
  • Invalid Expiration Date - not YYYYMM format
  • Invalid Compliance Date - such as a February 29 and not a leap year or a day of 31 in a month with less days

12. If the Import was successful, a report of the Import Totals appear:

• Click for the following:

 

13. A detailed report of the errors appear, if none the report is blank such as below:

14. When the import ASCII file is finished importing the following appears:

15. Click to return to the main menu: